﻿Create View [PerformanceMonitoring].[UnusedIndexes] As
  Select Top 100 Percent 
       Db_Name( dm_ius.database_id ) as DatabaseName
      ,o.name AS ObjectName
      ,i.name AS IndexName
      ,i.index_id AS IndexId
      ,dm_ius.user_seeks AS UserSeek
      ,dm_ius.user_scans AS UserScans
      ,dm_ius.user_lookups AS UserLookups
      ,dm_ius.user_updates AS UserUpdates
      ,Convert(Decimal(12,2), (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) / Convert(Decimal, IsNull(NullIf(dm_ius.user_updates, 0), 1))) As ReadWriteRatio
      ,p.TableRows
      ,'Drop Index ' + QuoteName(i.name) + ' On ' + QuoteName(s.name) + '.' + QuoteName(Object_Name(dm_ius.object_id)) As 'DropStatement'
    From sys.dm_db_index_usage_stats dm_ius
  Inner Join sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.object_id = i.object_id
  Inner Join sys.objects o ON dm_ius.object_id = o.object_id
  Inner Join sys.schemas s ON o.schema_id = s.schema_id
  Inner Join ( Select Sum(p.rows) TableRows, p.index_id, p.object_id
           From sys.partitions p 
          Group By p.index_id, p.object_id ) p On p.index_id = dm_ius.index_id And dm_ius.object_id = p.object_id
   Where ObjectProperty(dm_ius.object_id,'IsUserTable') = 1
     And i.type_desc = 'nonclustered'
     And i.is_primary_key = 0
     And i.is_unique_constraint = 0
   Order By Convert(Decimal(12,2), (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) / Convert(Decimal, IsNull(NullIf(dm_ius.user_updates, 0), 1))) Asc